﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;

namespace DAL
{
    public class UserLoginDAL : IUserLoginDAL
    {
        private static readonly string SQL_CONN_SQLSERVER = ConfigurationManager.AppSettings["DBConn"];



        /// <summary>
        /// 根据传入的userId判断一条数据是否存在
        /// </summary>       
        public bool userIdExists(int userId)
        {
            //Set up a return value
            bool isExist = false;
            string sqlStatement = "SELECT UL_userId FROM UserLogin WHERE UL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }


        /// <summary>
        /// 根据传入的userName判断一条数据是否存在
        /// </summary>       
        public bool userNameExists(string userName)
        {
            //Set up a return value
            bool isExist = false;
            string sqlStatement = "SELECT UL_userName FROM UserLogin WHERE UL_userName = @userName";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userName", userName);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }


        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(string userName, string password, System.DateTime lastLoginTime, int status)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "INSERT INTO UserLogin VALUES(@userName, @password,@lastLoginTime,@status )";


            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] {new SqlParameter("@userName", userName), new SqlParameter("@password", password), new SqlParameter("@lastLoginTime", lastLoginTime), new SqlParameter("@status", status) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.UserLogin userLogin)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "INSERT INTO UserLogin VALUES(@userId, @userName, @password,@lastLoginTime,@status )";


            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", userLogin.getUL_userId()), new SqlParameter("@userName", userLogin.getUL_userName()), new SqlParameter("@password", userLogin.getUL_password()), new SqlParameter("@lastLoginTime", userLogin.getUL_lastLoginTime()), new SqlParameter("@status", userLogin.getUL_status()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>          
        public bool Update(int userId, string userName, string password, System.DateTime lastLoginTime, int status)
        {     //Set up a return value
            bool opetation = false;
            string sqlStatement = "UPDATE UserLogin SET  UL_userName=@userName,UL_password=@password,UL_lastLoginTime=@lastLoginTime,UL_status=@status where UL_userId=@userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] {  new SqlParameter("@userName", userName), new SqlParameter("@password", password), new SqlParameter("@lastLoginTime", lastLoginTime), new SqlParameter("@status", status),  new SqlParameter("@userId", userId)};

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 修改用户信息
        /// </summary>
        public bool Update(Model.UserLogin userLogin)
        {     //Set up a return value
            bool opetation = false;
            string sqlStatement = "UPDATE UserLogin SET UL_userName=@userName,UL_password=@password,UL_lastLoginTime=@lastLoginTime,UL_status=@status WHERE UL_userId=@userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userName", userLogin.getUL_userName()), new SqlParameter("@password", userLogin.getUL_password()), new SqlParameter("@lastLoginTime", userLogin.getUL_lastLoginTime()), new SqlParameter("@status", userLogin.getUL_status()), new SqlParameter("@userId", userLogin.getUL_userId()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }



        /// <summary>
        /// 删除一个login通过ID
        /// </summary>
        public bool userIdDelete(int userId)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "DELETE FROM UserLogin WHERE UL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }



        /// <summary>
        /// 根据ID查询用户对象
        /// </summary>
        public Model.UserLogin getUserById(int userId)
        {

            //Set up a return value
            //bool opetation = false;
            Model.UserLogin userLogin = null;
            string sqlStatement = "SELECT * FROM UserLogin WHERE UL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                    {
                        userLogin.setUL_userId(Convert.ToInt32(rdr.GetInt32(0)));
                        userLogin.setUL_userName(Convert.ToString(rdr.GetString(1)));
                        userLogin.setUL_password(Convert.ToString(rdr.GetString(2)));
                        userLogin.setUL_lastLoginTime(Convert.ToDateTime(rdr.GetDateTime(3)));
                        userLogin.setUL_status(Convert.ToInt32(rdr.GetInt32(4)));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return userLogin;
        }

        public Model.UserLogin getUserByUsername(string username)
        {

            //Set up a return value
            //bool opetation = false;
            Model.UserLogin userLogin = new Model.UserLogin();
            string sqlStatement = "SELECT * FROM UserLogin WHERE UL_userName = @userName";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@username", username);

                //Execute the delete	
                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    while (rdr.Read())
                    {
                        userLogin.setUL_userId(Convert.ToInt32(rdr.GetInt32(0)));
                        userLogin.setUL_userName(Convert.ToString(rdr.GetString(1)));
                        userLogin.setUL_password(Convert.ToString(rdr.GetString(2)));
                        userLogin.setUL_lastLoginTime(Convert.ToDateTime(rdr.GetDateTime(3)));
                        userLogin.setUL_status(Convert.ToInt32(rdr.GetInt32(4)));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return userLogin;
        }


        /// <summary>
        /// 根据ID查看账号是否被限制
        /// </summary>
        public int isLimit(int userId)
        {
            //Set up a return value
            int status = 0;

            string sqlStatement = "SELECT UL_status FROM UserLogin WHERE UL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                    {
                        status = Convert.ToInt32(rdr.GetString(0));

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return status;
        }



        /// <summary>
        /// 根据名字查看账号是否被限制
        /// </summary>
        public int isLimit(string userName)
        {
            //Set up a return value
            int status = 0;

            string sqlStatement = "SELECT UL_status FROM UserLogin WHERE UL_userName = @userName";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userName", userName);

                //Execute the delete	
                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                    {
                        status = Convert.ToInt32(rdr.GetString(0));

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return status;
        }

    }
}
